Perinvo · HR & Business Smart Tools

Dashboard Overview & Objectives

An HR Dashboard consolidates critical workforce data into interactive visual analytics, enabling data-driven decision-making across three essential dimensions of human resource management.

Practical Example

Imagine you need to present workforce metrics to senior management. Instead of showing raw data tables, you create a dashboard with:

  • Headcount trend line chart showing monthly employee count changes
  • Department distribution pie chart visualizing workforce allocation
  • Turnover rate bar chart comparing departments
  • Key metrics summary with current headcount, turnover rate, average tenure

This visual presentation enables quick insights and informed decision-making.

Three Essential HR Dimensions

Personnel Management

  • Headcount & distribution
  • Turnover analysis
  • Department metrics
  • Workforce demographics
  • Attendance tracking

Payroll Analytics

  • Salary distribution
  • Payroll expenses
  • Deductions analysis
  • Cost center allocation
  • Budget vs. actual

Compensation Planning

  • Salary benchmarking
  • Bonus distribution
  • Incentive analysis
  • Equity metrics
  • Cost of living adjustments

Data Management for HR Dashboards

Data Sources & Collection Methods

HR dashboards rely on data from multiple systems. Primary sources include:

  • HRIS System: Employee master data, employment status, job classifications
  • Payroll System: Salary components, deductions, benefits, tax information
  • Attendance System: Daily records, leave balances, overtime hours
  • Performance Data: Appraisal scores, competency assessments

Data Collection Example

To create a monthly HR dashboard, you would:

  1. Export employee data from HRIS as CSV on the 1st of each month
  2. Export payroll data from payroll system after monthly payroll run
  3. Combine these files in Excel using VLOOKUP or Power Query
  4. Add manual data for new hires not yet in systems
  5. Run data quality checks before dashboard update

Data Cleaning & Validation

Before analysis, data must be cleaned and validated:

Excel Implementation Example

Removing Duplicates:

1. Select your data range 2. Go to Data tab → Remove Duplicates 3. Select columns to check (Employee ID, Name) 4. Click OK to remove duplicate records

Standardizing Formats:

1. For dates: Select column → Format Cells → Date → Choose MM/DD/YYYY 2. For department names: Use Find & Replace to standardize (e.g., replace "HR Dept" with "Human Resources")

Handling Missing Values:

1. Select range with blanks 2. Press F5 → Special → Blanks 3. Type formula or value to fill 4. Press Ctrl+Enter to fill all selected blanks
Pro Tip: Create a standardized data validation checklist that includes checking for unique employee IDs, valid date ranges (hire date ≤ today), and logical salary values (>0).

Data Transformation

Create calculated fields for dashboard metrics:

Excel Formula Examples

Tenure Calculation:

=INT((TODAY()-Hire_Date)/365.25) Or for years and months: =DATEDIF(Hire_Date, TODAY(), "y") & " years, " & DATEDIF(Hire_Date, TODAY(), "ym") & " months"

Salary Band Classification:

=IF(Salary > 100000, "High", IF(Salary > 60000, "Medium", "Low"))

YTD Earnings:

=SUMIFS(Payroll_Amount, Employee_ID, A2, Pay_Period, "<="&EOMONTH(TODAY(),0))

Pivot Tables for HR Analytics

Introduction to Pivot Tables

Pivot tables are powerful tools that transform raw data into meaningful summaries without complex formulas.

Practical HR Example

Scenario: You have 1,000 employee records with fields: Employee ID, Name, Department, Job Level, Salary, Hire Date, Location.

Task: Create a summary of headcount by department and average salary by job level.

Pivot Table Solution:

  1. Select your data range
  2. Go to Insert → Pivot Table → New Worksheet
  3. Drag "Department" to Rows area
  4. Drag "Employee ID" to Values area (set to Count)
  5. Result: Instant department headcount summary

For salary analysis, create a second pivot table:

  1. Drag "Job Level" to Rows area
  2. Drag "Salary" to Values area (set to Average)
  3. Result: Average salary by job level

Creating Pivot Tables - Step by Step

Excel Implementation

Step 1 - Select Data Range:

• Ensure your data has headers in the first row • No blank rows or columns within the data • Select the entire range (Ctrl+A or click the corner)

Step 2 - Insert Pivot Table:

1. Go to Insert tab → Pivot Table 2. Excel will auto-select your data range 3. Choose "New Worksheet" for clarity 4. Click OK

Step 3 - Configure Fields:

• Drag fields from Field List to areas below: • ROWS: Categories for grouping (Department, Job Title) • COLUMNS: Secondary categories (Year, Gender) • VALUES: Numbers to calculate (Salary, Count of Employees) • FILTERS: Top-level filters (Company, Status)

Step 4 - Format & Refresh:

• Apply number formatting ($, %, etc.) • Add Slicers for interactive filtering • Set refresh options (Data → Refresh All) • Apply Pivot Table Styles for professional look

Practical HR Pivot Table Examples

Personnel Headcount Dashboard

Configuration:

  • Rows: Department
  • Columns: Employment Status (Active, Inactive)
  • Values: Count of Employee ID
  • Filter: Location (optional)

Result: Department-wise headcount breakdown by status

Payroll Distribution Analysis

Configuration:

  • Rows: Department
  • Columns: Salary Grade
  • Values: Sum of Salary
  • Filter: Pay Period (Monthly/Quarterly)

Result: Payroll allocation by department and grade

Best Practice: Create separate pivot tables for different analyses rather than overly complex multi-field tables. This improves performance and readability.

Selecting Professional Chart Types

Basic Chart Types for HR Analytics

Bar Charts - For Comparisons

HR Use Case: Comparing salary across departments or headcount by job level.

Excel Implementation:

  1. Select your pivot table data
  2. Go to Insert → Charts → Column or Bar Chart
  3. Choose 2-D Clustered Column for simple comparisons
  4. Format: Add data labels, adjust colors, add chart title

Example: Department salary comparison bar chart shows Marketing has highest average salary, Administration the lowest.

Line Charts - For Trends

HR Use Case: Tracking headcount changes over 12 months or payroll expenses quarterly.

Excel Implementation:

  1. Select time-series data (months in rows, values in columns)
  2. Go to Insert → Charts → Line Chart
  3. Choose "Line with Markers" for clear data points
  4. Add trendline: Right-click data series → Add Trendline

Example: Headcount trend line shows seasonal hiring spikes in Q1 and Q3 each year.

Advanced Chart Types

Pie/Donut Charts - For Composition

HR Use Case: Showing salary component breakdown or department cost allocation.

Excel Implementation:

  1. Select data with categories and values
  2. Go to Insert → Charts → Pie or Doughnut Chart
  3. Limit to 5-6 slices maximum for readability
  4. Add data labels with percentages
  5. Explode important slices if needed

Best Practice: Use donut charts instead of pie charts for better visual appeal and space for center labels.

Heat Maps - For Multi-dimensional Data

HR Use Case: Salary equity analysis (job title vs. gender) or turnover rates by department and level.

Excel Implementation using Conditional Formatting:

  1. Create a matrix table (departments in rows, job levels in columns)
  2. Fill cells with turnover rates or salary ratios
  3. Select the data range
  4. Go to Home → Conditional Formatting → Color Scales
  5. Choose Green-Yellow-Red color scale
  6. Adjust rules: Green for low turnover, Red for high

Example: Heat map reveals Engineering department has high turnover at junior levels (red cells), stable at senior levels (green cells).

Building HR Dashboards

Personnel Dashboard

Tracks headcount, turnover, and workforce demographics.

Implementation Steps

Step 1 - Data Preparation:

Extract employee master data with fields: • Employee ID, Name, Department • Hire Date, Termination Date (if applicable) • Job Level, Status (Active/Inactive) • Location, Gender, Birth Date

Step 2 - Create Pivot Tables:

Create separate pivot tables for: 1. Headcount by Department (Rows: Dept, Values: Count) 2. Headcount Trend by Month (Rows: Month, Values: Count) 3. Turnover by Department (Rows: Dept, Columns: Reason) 4. Demographics by Age Group (Rows: Age Group, Values: Count)

Step 3 - Calculate Metrics:

Key formulas: • Turnover Rate = (Terminations / Avg Headcount) * 100 • Average Tenure = AVERAGE(TODAY() - Hire_Date) • Headcount Growth = (Current HC - Previous HC) / Previous HC

Step 4 - Build Visualizations:

  • Line chart for headcount trend
  • Bar chart for department headcount
  • Pie chart for status distribution
  • Heat map for turnover by department

Payroll Dashboard

Monitors salary distribution, expenses, and budget compliance.

Implementation Steps

Step 1 - Data Extraction:

Export payroll data with: • Employee ID, Department, Cost Center • Salary Components (Base, Allowances, Bonus) • Deductions (Tax, Insurance, Loans) • Pay Period, Payment Date

Step 2 - Pivot Table Creation:

Create pivot tables for: 1. Salary by Grade/Department 2. Monthly Payroll Expenses 3. Deduction Breakdown 4. Budget vs. Actual Comparison

Step 3 - Calculated Metrics:

Key formulas: • Cost per Employee = Total Payroll / Headcount • Salary Percentile = PERCENTILE.INC(Salary_Range, 0.75) • Budget Variance = (Actual - Budget) / Budget • Benefits % = Total Benefits / Total Compensation

Step 4 - Dashboard Assembly:

  • Place charts on a dedicated dashboard sheet
  • Add slicers for Department, Pay Period, Grade
  • Create a summary metrics area with KPI cards
  • Link all elements to pivot tables

Compensation Dashboard

Analyzes salary equity, benchmarking, and incentive distribution.

Implementation Steps

Step 1 - Data Preparation:

Combine data from HRIS and payroll: • Employee demographics (Gender, Age, Tenure) • Compensation details (Salary, Bonus, Equity) • Job information (Title, Level, Department) • Performance ratings

Step 2 - Benchmark Research:

Gather market salary data: • Industry surveys (Willis Towers Watson, Mercer) • Government statistics • Competitor analysis Create reference tables by Job Level × Location

Step 3 - Calculate Equity Metrics:

Key formulas: • Compa-Ratio = Actual Salary / Midpoint Salary • Gender Pay Gap = (Avg_Male - Avg_Female) / Avg_Male • Salary Equity Index = (Min_Salary / Max_Salary) × 100 • Incentive Ratio = Bonus / Base Salary

Step 4 - Visualization:

  • Scatter plot: Compa-Ratio vs. Performance
  • Bar chart: Bonus distribution by department
  • Heat map: Salary equity by job level × gender
  • Line chart: Market vs. internal salary trends

Professional Formatting & Design

Color Scheme Strategy

Use a limited, professional color palette for HR dashboards.

Primary: Deep Blue (#1F4E79)
Secondary: Professional Blue (#2E75B6)
Accent: Green (#70AD47)
Highlight: Yellow (#FFC000)

Excel Implementation

Applying Consistent Colors:

  1. Create a color theme in Excel:
    Page Layout → Colors → Customize Colors Set: • Text/Background Dark 1: #1F4E79 • Accent 1: #2E75B6 • Accent 2: #70AD47 • Accent 3: #FFC000
  2. Apply to charts:
    Right-click chart → Format Data Series → Fill → Solid Fill → Choose from theme colors
  3. Format cells:
    Select cells → Home → Fill Color → Choose from theme colors

Layout & Typography Guidelines

Dashboard Layout Example

Effective Dashboard Structure:

[Header: Company Logo + Dashboard Title] [Top Row: Key Metrics/KPI Cards] • Total Headcount: 1,245 • Turnover Rate: 8.2% • Avg Salary: $72,500 • Payroll Cost: $4.2M [Middle Row: Charts - Left Section] • Chart 1: Headcount Trend (Line Chart) • Chart 2: Department Distribution (Bar Chart) [Middle Row: Charts - Right Section] • Chart 3: Salary by Grade (Column Chart) • Chart 4: Turnover Heat Map [Bottom Row: Detailed Data] • Pivot Table: Employee List with Filters • Slicers: Department, Location, Period [Footer: Data Source & Last Updated]
Design Best Practices: Use white space effectively, align elements to a grid, maintain consistent spacing, left-align text for readability, and create clear visual hierarchy.

Interactive Elements

Excel Implementation

Adding Slicers for Filtering:

1. Click on your Pivot Table 2. Go to PivotTable Analyze → Insert Slicer 3. Choose fields: Department, Location, Year 4. Format slicers to match dashboard colors 5. Connect slicers to multiple pivot tables: Right-click slicer → Report Connections → Check all relevant pivot tables

Using Data Validation Dropdowns:

1. Select cell for dropdown 2. Data → Data Validation → Allow: List 3. Source: =$A$2:$A$10 (or type items separated by commas) 4. Use dropdowns for parameter selection in formulas

Conditional Formatting for Highlights:

1. Select data range 2. Home → Conditional Formatting 3. Choose rule type: • Highlight Cells Rules (Greater Than, Between) • Top/Bottom Rules (Top 10%, Above Average) • Data Bars / Color Scales / Icon Sets 4. Set format and threshold values

HR Dashboard Assessment

Test your knowledge with 30 multiple-choice questions. Select your answer for each question, then click Submit All Answers to see your score and review the correct answers.

Question 1 of 30